USE [ATAChietkhau]
GO

/****** Object:  StoredProcedure [dbo].[sprTheodoiATAFastNhaThauReport]    Script Date: 07/25/2010 21:16:12 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[sprTheodoiATAFastNhaThauReport]
	-- Add the parameters for the stored procedure here
	@BranchID uniqueidentifier, 
	@Month INT,
	@Year INT,
	@BrandID INT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	DECLARE @vReportDate DATETIME
	SELECT @vReportDate = dbo.Date(@Year, @Month, 1)

	DECLARE @TrungTamName NVARCHAR(255)
	DECLARE @TrungTamCode NVARCHAR(20)

	SELECT @TrungTamName = BranchName, @TrungTamCode = BranchCode FROM vwMisaBranches WHERE BranchID = @BranchID

	DECLARE @BrandName NVARCHAR(50)
	DECLARE @BrandCode NVARCHAR(50)
	SELECT @BrandCode = BrandCode, @BrandName = BrandName FROM dbo.ThuongHieu WHERE BrandID = @BrandID

    -- Insert statements for procedure here
	CREATE TABLE #tblTheodoiATAFastNhaThauReport
	(
		TenTrungTam NVARCHAR(255),
		KhachHangID UNIQUEIDENTIFIER,
		MaKH NVARCHAR(100),
		TenKH NVARCHAR(100),
		LEVEL INT,
		ParentKhachHangID UNIQUEIDENTIFIER,
		TenChuongTrinh NVARCHAR(200),
		Thang INT,
		Nam INT,
		MucChietKhauTD DECIMAL,
		MucChietKhauCC DECIMAL,
		TenThuongHieu NVARCHAR(10),
		DuThuongID UNIQUEIDENTIFIER,
		StartDate DATETIME,
		DoanhSoCamKet MONEY,
		TenPhanThuong NVARCHAR(50),
		GiaTriPhanThuong DECIMAL
		--DoanhSoJUT MONEY,
		--DoanhSoHIL MONEY,
		--DoanhSoFAL MONEY,
		--DoanhSoVIE MONEY,
		--DoanhSoJAJ MONEY,
		--DoanhSoATA MONEY,
		--TongDoanhSo MONEY
	)


	INSERT INTO #tblTheodoiATAFastNhaThauReport(TenTrungTam, KhachHangID, MaKH, TenKH, LEVEL, ParentKhachHangID, TenChuongTrinh, Thang, Nam, TenThuongHieu, DuThuongID, 
	StartDate, MucChietKhauTD, MucChietKhauCC, TenPhanThuong, GiaTriPhanThuong)
	SELECT @TrungTamName AS TenTrungTam, D.KhachHangID, D.MaKH, D.TenKH, D.Level, D.ParentID AS ParentKhachHangID, A.TenChuongTrinh, @Month AS Thang, @Year AS Nam, @BrandName AS TenThuongHieu, 
	DuThuongID, A.StartDate, A.MucChietKhauTD, A.MucChietKhauCC, TenPhanThuong, GiaTriPhanThuong
	FROM vwAllCustomers D 
	LEFT JOIN (
	SELECT A.HoiVienID, C.TenChuongTrinh, B.BrandCode AS TenThuongHieu, 
	A.ID AS DuThuongID, A.StartDate, A.MucChietKhauTD, A.MucChietKhauCC, D.NAME AS TenPhanThuong, D.value AS GiaTriPhanThuong
	FROM KhachHangDuthuong A, ThuongHieu B, ChuongTrinh C, PhanThuong D
	WHERE A.BrandID = B.BrandID 
	AND A.ChuongTrinhID = C.ChuongTrinhID
	AND A.PhanThuongID = D.ID
	AND A.StartDate <= @vReportDate
	AND DATEADD(mm, A.Duration, A.StartDate) > @vReportDate
	) A ON A.HoiVienID = D.KhachHangID
	WHERE 
	(D.BranchID = @BranchID OR D.BranchCode LIKE '%' + @TrungTamCode + '%')
	--AND D.LEVEL = 2

	-- Lay doanh so theo thuong hieu
	-- Jutuner
	--SELECT ds.AccountingObjectID, SUM(ds.DoanhSoTrongThang) AS DoanhSoTrongThang 
	--FROM vwGetAllDoanhSo ds, #tblTheodoiATAFastNhaThauReport kh
	--WHERE ds.PhanLoaiHang LIKE + 'JUN-%'
	--AND MONTH(ds.NgayHoaDon) = @Month AND YEAR(ds.NgayHoaDon) = @Year
	--GROUP BY ds.AccountingObjectID


	SELECT 
	khdt.*, 
	DoanhSoTong.DoanhSoTD,
	DoanhSoTong.DoanhSoCC
	FROM #tblTheodoiATAFastNhaThauReport khdt
	LEFT JOIN (
	SELECT ds.KhachHangID, SUM(ds.DoanhSoTD) AS DoanhSoTD, SUM(ds.DoanhSoCC) AS DoanhSoCC
	FROM vwGetTotalMonthlyRevenue ds
	WHERE ds.Thang = @Month AND ds.Nam = @Year
	AND ThuongHieu LIKE @BrandCode
	GROUP BY ds.KhachHangID) DoanhSoTong ON khdt.KhachHangID = DoanhSoTong.KhachHangID
	ORDER BY khdt.TenChuongTrinh DESC


	DROP TABLE #tblTheodoiATAFastNhaThauReport

	-- exec sprTheodoiATAFastNhaThauReport '0A110906-E0A3-46AB-B716-4F56085945D5', 4, 2010, 1
END


GO


